﻿Imports HtmlAgilityPack
Imports System.Data.SQLite

Module Languages

	Public Function GetLanguages() As List(Of KeyValuePair(Of String, String))
		Dim returnList As New List(Of KeyValuePair(Of String, String))

		Using conn As New SQLiteConnection(My.Settings.OfflineDataConnectionString)
			conn.Open()
			Dim sql As String = "SELECT LanguageCode, Language FROM Languages ORDER BY LanguageCode"
			Dim cmd As New SQLiteCommand(sql, conn)

			Using reader As SQLiteDataReader = cmd.ExecuteReader
				While reader.Read
					returnList.Add(New KeyValuePair(Of String, String)(reader("LanguageCode"), reader("Language")))
				End While

			End Using
		End Using

		Return returnList
	End Function

	Public Sub UpdateLanguages()
		Dim allLanguages As List(Of KeyValuePair(Of String, String)) = DownloadLanguages()

		Using conn As New SQLiteConnection(My.Settings.OfflineDataConnectionString)
			conn.Open()
			Using trans As SQLiteTransaction = conn.BeginTransaction
				For Each language In allLanguages
					Dim sql As String = "INSERT OR REPLACE INTO Languages (LanguageCode, Language) VALUES (@LanguageCode, @Language)"
					Dim cmd As New SQLiteCommand(sql, conn)

					cmd.Parameters.AddWithValue("@LanguageCode", language.Key)
					cmd.Parameters.AddWithValue("@Language", language.Value)

					cmd.ExecuteNonQuery()
				Next

				trans.Commit()
			End Using
		End Using
	End Sub

	Public Function DownloadLanguages() As List(Of KeyValuePair(Of String, String))
		Dim returnList As New List(Of KeyValuePair(Of String, String))

		Dim pageGetter As New HtmlWeb
		Dim page As HtmlDocument = pageGetter.Load("http://wol.jw.org/en/wol/li/r1/lp-e")
		Dim allLanguages As HtmlNodeCollection = page.DocumentNode.SelectSingleNode("//ul[@class='librarySelection']").SelectNodes("li[@data-lang]")

		For Each languageItem In allLanguages
			Dim langaugeCode As String = languageItem.SelectSingleNode("a").GetAttributeValue("href", "").Split("/")(1)
			Dim language As String = languageItem.SelectSingleNode("descendant::li[@class='library']").InnerText
			returnList.Add(New KeyValuePair(Of String, String)(langaugeCode, language))
		Next

		Return returnList
	End Function
End Module
